import 'package:sqflite/sqflite.dart'; //数据库
import 'dart:io'; //本地文件使用
import 'dart:async';//异步操作
import 'package:path/path.dart';//本地路径访问
import 'package:path_provider/path_provider.dart';
import 'package:stock_check/common/db/ITModel.dart';
import 'package:stock_check/common/db/InstitutionModel.dart';
import 'package:stock_check/common/db/TaskModel.dart';
import 'package:stock_check/common/db/uploadTaskItemModel.dart';//本地路径访问


class DataBase{
  Database _myDataBase ;
  final String itTableName = 'itTable';
  final String institutionName = 'institionTable';
  final String taskTableName = 'taskTable';
  final String uploadTaskTableName = 'uploadTask';
  final String dbName = 'asset.db';

  // 获取数据库
  Future get db async {
    if(_myDataBase!=null){
      return _myDataBase;
    }else{
      _myDataBase = await initDb();
      return _myDataBase;
    }
  }

  initDb() async {
    Directory directory = await getApplicationDocumentsDirectory();
    String path = join(directory.path, dbName);
    var database = await openDatabase(path, version: 1, onCreate: _onCreate);
    return database;
  }

  FutureOr _onCreate(Database db, int version) async{
    var batch = db.batch();
    batch.execute('''create table $taskTableName(
      id INTEGER primary key autoincrement,
      updateTime text,
      jsonTxt text)''');
    // print('盘点任务数据创建完成！');
    batch.execute('''create table $uploadTaskTableName(
      id INTEGER primary key autoincrement,
      taskId INTEGER,
      jsonTxt text)
    ''');
    // print('待盘点数据数据库创建成功!');

    batch.execute('''create table $itTableName(
      id INTEGER primary key autoincrement,
      updateTime text,
      jsonTxt text)''');
    // print('IT数据库创建完成！');
    batch.execute('''create table $institutionName(
      id INTEGER primary key autoincrement,
      updateTime text,
      jsonTxt text)''');
    // print('设备数据库创建完成！');
    await batch.commit(noResult: true);
  }

  // 保存待上传的盘点信息
  Future<int> insertUploadTask(UploadTaskItemModel ut) async{
    Database database = await db;
    var res = database.insert(uploadTaskTableName, UploadTaskItemModel.toJson(ut));
    // print('数据已插入!');
    return res;
  }

  // 查询待上传的盘点信息
  Future<UploadTaskItemModel> findTaskDetailById(int taskId) async{
    Database database = await db;
    var res = await database.rawQuery('select * from $uploadTaskTableName where taskId = $taskId');
    if(res.length==0){
      return UploadTaskItemModel.empty();
    }else{
      return UploadTaskItemModel.fromJson(res[0]);
    }
  }

  // 删除待上传的盘点信息
  Future<int> deleteUploadModelById(int taskId) async{
    Database database = await db;
    var res = await database.rawDelete('delete from $uploadTaskTableName where taskId=$taskId');
    return res;
  }

  // 删除所有待上传信息
  Future<int> deleteAllUploadModelById(int taskId) async{
    Database database = await db;
    var res = await database.delete(uploadTaskTableName);
    // print('数据删除成功!');

    return res;
  }

  // 更新等待上传的信息
  Future<int> updateUploadModelById(UploadTaskItemModel u) async{
    Database database = await db;
    var res = await database.rawUpdate('''update $uploadTaskTableName set jsonTxt = ${u.jsonTxt}
    where taskId = ${u.taskId}''');

    return res;
  }


  //插入盘点任务
  Future<int> insertTaskModel(TaskModel tm) async{
    //获取数据库实例
    Database database = await db;
    var res = database.insert(taskTableName, TaskModel.toJson(tm));
    // print('数据已插入');
    return res;
  }

  //更新盘点任务列表
  Future<int> updateTaskModel(TaskModel tm)async{
    Database database = await db;
    var result = database.update(taskTableName, TaskModel.toJson(tm),
        where: "updateTime = ?", whereArgs: [tm.updateTime]);

    return result;
  }

  // 获取单个盘点
  Future<TaskModel> getOneTaskByUpdateTime(String updateTime) async{
    Database database = await db;
    var result = await database
        .rawQuery('select * from $taskTableName where updateTime = "$updateTime"');
    if(result.length == 0){
      return TaskModel();
    }else{
      // print(result[0]);
      return TaskModel.fromJson(result[0]);
    }
  }

  // 删除盘点数据
  Future<int> deleteTaskTable() async {
    Database database = await db;
    database.delete(taskTableName);
    // print('数据删除成功！');
    return 1;
  }


  //插入IT资产
  Future<int> insertITModel(ITModel it) async {
    //获取数据库实例
    Database database = await db;
    var result = database.insert(itTableName, ITModel.toJson(it));
    // print('数据已插入');
    return result;
  }


  //插入设施资产
  Future<int> insertInstitutionModel(InstitutionModel im) async {
    //获取数据库实例
    Database database = await db;
    //diy.toMap()是将diy实例转换成字段名和值对应的map
    var result = database.insert(institutionName, InstitutionModel.toJson(im));
    // print('数据已插入');
    return result;
  }

  //获取所有IT资产总数
  Future<int> getITCount() async {
    Database database = await db;
    var res = await database.rawQuery('select count(*) from $itTableName');

    return res[0]['count(*)'];
  }


  Future<List<ITModel>> selectAllITModel() async {
    Database database = await db;
    var result = await database.rawQuery('select * from $itTableName');

    List<ITModel> list = new List();
    for(int i=0;i<result.length;i++){
      list.add(ITModel.fromJson(result[i]));
    }
    return list;
  }

  // 获取单个IT资产
  Future<ITModel> getOneITByUpdateTime(String updateTime) async{
    Database database = await db;
    var result = await database
    .rawQuery('select * from $itTableName where updateTime = "$updateTime"');
    if(result.length == 0){
      return ITModel();
    }else{
      return ITModel.fromJson(result[0]);
    }
  }


  //获取所有设施资产总数
  Future<int> getInstitutionCount() async {
    Database database = await db;
    var res = await database.rawQuery('select count(*) from $institutionName');

    return res[0]['count(*)'];
  }

  // 获取单个设施资产
  Future<InstitutionModel> getOneInstitutionByUpdateTime(String updateTime) async{
    Database database = await db;
    var result = await database
    .rawQuery('select * from $institutionName where updateTime = "$updateTime"');

    if(result.length == 0){
      return InstitutionModel();
    }else{
      return InstitutionModel.fromJson(result[0]);
    }
  }

  // 更新IT资产
  Future<int> updateITModel(ITModel it)async{
    Database database = await db;
    var result = database.update(itTableName, ITModel.toJson(it),
    where: "updateTime = ?", whereArgs: [it.updateTime]);

    return result;
  }

  // 更新设备资产
  Future<int> updateInstitutionModel(InstitutionModel it)async{
    Database database = await db;
    var result = database.update(institutionName, InstitutionModel.toJson(it),
    where: "updateTime = ?", whereArgs: [it.updateTime]);

    return result;
  }

  // 批量删除数据
  Future<int> deleteTable() async {
    Database database = await db;
    var batch = database.batch();
    batch.rawDelete('delete from $itTableName');
    batch.rawDelete('delete from $institutionName');

    await batch.commit(noResult: true);

    // print('数据删除成功！');

    return 1;
  }
}